package com.neuedu.oasystem.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neuedu.oasystem.dao.IPlanAuditDao;
import com.neuedu.oasystem.entity.PlanAudit;
import com.neuedu.oasystem.util.DBHelper;

/**
 * 计划审批表接口实现类
 * @author admin hd
 *
 */
public class PlanAuditDaoImpl extends BaseDao implements IPlanAuditDao {

	@Override
	public boolean add(PlanAudit param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		String sql = "insert into plan_audit(plan_id,f_ad,fin_audit_res,m_ad,mar_audit_res) values(?,?,?,?,?)";
		
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, param.getPlanId());
		stmt.setString(2, param.getFad());
		stmt.setString(3, param.getFinAuditRes());
		stmt.setString(4, param.getMad());
		stmt.setString(5, param.getMgrAuditRes());

		int count = stmt.executeUpdate();
		if(count==1) {
			return true;
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	@Override
	public boolean edit(PlanAudit param) {
		Connection conn =DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		String sql = " update plan_audit set f_ad=?,fin_audit_res=?,m_ad=?,mar_audit_res=? where plan_id=?";
		
		stmt = conn.prepareStatement(sql);
		stmt = conn.prepareStatement(sql);
		stmt.setString(5, param.getPlanId());
		stmt.setString(1, param.getFad());
		stmt.setString(2, param.getFinAuditRes());
		stmt.setString(3, param.getMad());
		stmt.setString(4, param.getMgrAuditRes());
		int count = stmt.executeUpdate();
		if(count==1) {
			return true;
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;

	}

	@Override
	public boolean deleteById(int id) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public PlanAudit findById(int id) {
		PlanAudit paudit = null;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select plan_id,f_ad,m_ad from plan_audit where plan_id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				String planId = rs.getString("plan_id");
				String fad = rs.getString("f_ad");
				String finAuditRes = rs.getString("fin_audit_res");
				String mad = rs.getString("m_ad");
				String mgrAuditRes = rs.getString("mgr_audit_res");
				paudit = new PlanAudit(planId,fad,finAuditRes,mad,mgrAuditRes);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return paudit;
	}

	@Override
	public List<PlanAudit> findAll() {
		List<PlanAudit> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select plan_id,f_ad,fin_audit_res,m_ad,mar_audit_res from plan_audit";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()) {
				String planId = rs.getString("plan_id");
				String fad = rs.getString("f_ad");
				String finAuditRes = rs.getString("fin_audit_res");
				String mad = rs.getString("m_ad");
				String mgrAuditRes = rs.getString("mar_audit_res");
				PlanAudit audit = new PlanAudit(planId,fad,finAuditRes,mad,mgrAuditRes);
				list.add(audit);
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	@Override
	public List<PlanAudit> findByExample(PlanAudit param) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<PlanAudit> findByPager(int page, int size) {
		List<PlanAudit> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from "
					+"(select rownum rn,plan_id,f_ad,fin_audit_res,m_ad,mgr_audit_res from plan_audit where rownum<=? ) where rn >?";
			stmt = conn.prepareStatement(sql);
			
			stmt.setInt(1, page*size);
			stmt.setInt(2, (page-1)*size);
			
			rs = stmt.executeQuery();
			while(rs.next()) {
				String planId = rs.getString("plan_id");
				String fad = rs.getString("f_ad");
				String finAuditRes = rs.getString("fin_audit_res");
				String mad = rs.getString("m_ad");
				String mgrAuditRes = rs.getString("mgr_audit_res");
				PlanAudit audit = new PlanAudit(planId,fad,finAuditRes,mad,mgrAuditRes);
				list.add(audit);
		}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	@Override
	public int deleteByIds(int[] ids) {
		int result = 0;
		Connection connection = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
		connection.setAutoCommit(false);
		String sql = "delete from plan_audit where project_id =?";
		stmt = connection.prepareStatement(sql);
		for(int id : ids) {
			stmt.setInt(1, id);
			stmt.addBatch();
		}
			int[] counts = stmt.executeBatch();
			for(int i : counts){
				if(i==1) {
					result++;
				}
			}
		
			if(result==ids.length) {
				connection.commit();
				connection.setAutoCommit(true);
			}else {
				connection.rollback();
			}
		}catch(SQLException e) {
			e.printStackTrace();
			if(connection!=null) {
			try{
				connection.rollback();
				connection.setAutoCommit(true);
			}catch(SQLException e1) {
				
			}
			}
		}finally {
			DBHelper.closeAll(null, stmt, connection);
		}
		return result;
	}

	@Override
	public int addAll(List<PlanAudit> useparamrs) {
		int result = 0;
		Connection connection = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			connection.setAutoCommit(false);
			String sql = "insert into plan_audit(plan_id,f_ad,fin_audit_res,m_ad,mgr_audit_res) values(?,?,?,?,?)";
			stmt = connection.prepareStatement(sql);
			for (PlanAudit audit:useparamrs) {
				stmt.setString(1, audit.getPlanId());
				stmt.setString(2, audit.getFad());
				stmt.setString(3, audit.getFinAuditRes());
				stmt.setString(4, audit.getMad());
				stmt.setString(5, audit.getMgrAuditRes());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == 1) {
					result++;
				}
			}
			if (result == useparamrs.size()) {
				connection.commit();
				connection.setAutoCommit(true);
			} else {
				connection.rollback();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			if (connection != null) {
				try {
					connection.rollback();
					connection.setAutoCommit(true);
				} catch (SQLException e1) {
				}
			}

		} finally {
			DBHelper.closeAll(null, stmt, connection);
		}
		return result;
	}

	@Override
	public int editAll(List<PlanAudit> param) {
		return 0;
	}
	
}
